"""
Case Type   : 客户场景-邮储
Case Name   : enable_pbe_optimization=off时检查prepare执行结果
Create At   : 2021/11/25
Owner       : wan005
Description :
    1.设置enable_pbe_optimization=off
    2.创建表
    3.创建插入prepare并执行
    4.查询表数据
    5.创建更新prepare并执行
    6.查询表数据
    7.创建删除prepare语句并执行
    8.查询表数据
    9.创建匿名块
    10.查询表数据
Expect      :
    1.设置参数成功
    2.创建表成功
    3.创建prepare语句成功，且可正常执行
    4.查询数据符合预期
    5.创建prepare语句成功，且可正常执行
    6.查询数据符合预期
    7.创建prepare语句成功，且可正常执行
    8.查询数据符合预期
    9.创建匿名块成功
    10.查询数据符合预期
History     :
"""
import unittest
import os

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Common import Common
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class Finance(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.constant = Constant()
        self.commonsh = CommonSH("PrimaryDbUser")
        self.common = Common()
        self.user_node = Node("PrimaryDbUser")
        self.root_node = Node("PrimaryRoot")
        status = self.commonsh.get_db_cluster_status("detail")
        self.log.info(status)
        self.assertTrue("Normal" in status or "Degraded" in status)
        status = self.commonsh.restart_db_cluster()
        self.log.info(status)
        status = self.commonsh.get_db_cluster_status("detail")
        self.log.info(status)
        self.assertTrue("Normal" in status or "Degraded" in status)

        result = self.commonsh.execut_db_sql("show enable_opfusion")
        self.assertNotIn("ERROR", result)
        self.enable_opfusion = result.splitlines()[-2].strip()
        self.log.info(self.enable_opfusion)
        result = self.commonsh.execut_db_sql("show enable_bitmapscan")
        self.assertNotIn("ERROR", result)
        self.enable_bitmapscan = result.splitlines()[-2].strip()
        self.log.info(self.enable_bitmapscan)
        result = self.commonsh.execut_db_sql("show enable_seqscan")
        self.assertNotIn("ERROR", result)
        self.enable_seqscan = result.splitlines()[-2].strip()
        self.log.info(self.enable_seqscan)
        result = self.commonsh.execut_db_sql("show opfusion_debug_mode")
        self.assertNotIn("ERROR", result)
        self.opfusion_debug_mode = result.splitlines()[-2].strip()
        self.log.info(self.opfusion_debug_mode)
        result = self.commonsh.execut_db_sql("show enable_pbe_optimization")
        self.assertNotIn("ERROR", result)
        self.enable_pbe_optimization = result.splitlines()[-2].strip()
        self.log.info(self.enable_pbe_optimization)

        self.t_name = "t_finance_009"

    def test_finance(self):
        text = f"-----step1:修改参数; expect:成功-----"
        self.log.info(text)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          "enable_opfusion=on")
        self.assertTrue(res, "执行失败:" + text)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          "enable_bitmapscan=off")
        self.assertTrue(res, "执行失败:" + text)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          "enable_seqscan=off")
        self.assertTrue(res, "执行失败:" + text)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          "opfusion_debug_mode='log'")
        self.assertTrue(res, "执行失败:" + text)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          "enable_pbe_optimization=off")
        self.assertTrue(res, "执行失败:" + text)

        text = f"-----step2:创建表; expect:成功-----"
        self.log.info(text)
        sql = f"create table {self.t_name}(c_int int, " \
              f"c_money money default -12345, c_text text, " \
              f"c_char char(20), c_clob clob);"
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS,
                      result, "执行失败:" + text)

        text = "-----step3:创建插入prepare并执行; expect:创建prepare语句成功，且可正常执行-----"
        self.log.info(text)
        sql = f'''prepare insert_int_clob(int,clob) as \
                insert into {self.t_name} values(\$1, (+123)::money, \
                '^^呜啦啦', 'biubiubiu~~~', \$2);\
                execute insert_int_clob(1, 'dyd');\
                execute insert_int_clob(2, 'dyd');\
                execute insert_int_clob(3, 'dyd');'''
        self.log.info(sql)
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败:" + text)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, result, "执行失败:" + text)
        self.assertIn("PREPARE", result, "执行失败:" + text)

        text = "-----step4:查询表数据;expect:查询数据符合预期-----"
        self.log.info(text)
        sql = f"select * from {self.t_name};"
        self.log.info(sql)
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn("$123.00", result, "执行失败:" + text)
        self.assertIn("^^呜啦啦", result, "执行失败:" + text)
        self.assertIn("biubiubiu~~~", result, "执行失败:" + text)
        self.assertIn("dyd", result, "执行失败:" + text)
        self.assertIn("3 rows", result, "执行失败:" + text)

        text = "-----step5:创建更新prepare并执行; expect:创建prepare语句成功，且可正常执行-----"
        self.log.info(text)
        sql = f'''prepare update_int_char(int,char(20)) \
                as update {self.t_name} set c_int=\$1,\
                c_char=\$2 where c_int<100;\
                execute update_int_char(20,'&&-');'''
        self.log.info(sql)
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败:" + text)
        self.assertIn(self.constant.UPDATE_SUCCESS_MSG, result, "执行失败:" + text)
        self.assertIn("PREPARE", result, "执行失败:" + text)

        text = "-----step6:查询表数据;expect:查询数据符合预期-----"
        self.log.info(text)
        sql = f"select * from {self.t_name};"
        self.log.info(sql)
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn("$123.00", result, "执行失败:" + text)
        self.assertIn("^^呜啦啦", result, "执行失败:" + text)
        self.assertIn("20", result, "执行失败:" + text)
        self.assertIn("dyd", result, "执行失败:" + text)
        self.assertIn("&&-", result, "执行失败:" + text)
        self.assertIn("3 rows", result, "执行失败:" + text)

        text = "-----step7:创建更新prepare并执行; expect:创建prepare语句成功，且可正常执行-----"
        self.log.info(text)
        sql = f'''prepare delete_text(int, text) \
            as delete from {self.t_name} \
            where c_text=\$2 and c_int<\$1;\
            execute delete_text(110,'^^呜啦啦');'''
        self.log.info(sql)
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败:" + text)
        self.assertIn(self.constant.DELETE_SUCCESS_MSG, result, "执行失败:" + text)
        self.assertIn("PREPARE", result, "执行失败:" + text)

        text = "-----step8:查询表数据;expect:查询数据符合预期-----"
        self.log.info(text)
        sql = f"select * from {self.t_name};"
        self.log.info(sql)
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn("0 rows", result, "执行失败:" + text)

        text = "-----step9:创建匿名块; expect:成功-----"
        self.log.info(text)
        sql = f'''declare v_c record;
            begin
                prepare insert_int_clob(int,clob) as insert into 
                {self.t_name} values(\$1, (-23.567)::money, 
                'l 看不jian', '%dc', \$2);
                prepare update_int_char(int,char(20)) as update 
                {self.t_name} set c_int=\$1,c_char=\$2 where 
                c_int<100 and c_clob='dyd';
                prepare delete_text(int, text) as delete from 
                {self.t_name} where c_text=\$2 and c_int<\$1;
                for v_c in select id from generate_series(98,291) as id loop
                    execute 'execute insert_int_clob('||v_c.id||', ''df'')';
                    execute 'execute update_int_char('||v_c.id||', ''dyd'')';
                    execute 'execute delete_text(100,''l 看不jian'')';
                end loop;
            end;'''
        self.log.info(sql)
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.constant.ANONYMOUS_BLOCK_EXECUTE_SUCCESS_MSG,
                      result, "执行失败:" + text)

        text = "-----step10:查询表数据;expect:查询数据符合预期-----"
        self.log.info(text)
        sql = f"select * from {self.t_name};"
        self.log.info(sql)
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn("192 rows", result, "执行失败:" + text)

    def tearDown(self):
        text = "-----step11:清理环境; expect:成功-----"
        self.log.info(text)
        result_list = []
        res = self.commonsh.execute_gsguc("reload",
        self.constant.GSGUC_SUCCESS_MSG,
        f"enable_opfusion={self.enable_opfusion}")
        result_list.append(res)
        res = self.commonsh.execute_gsguc("reload",
                            self.constant.GSGUC_SUCCESS_MSG,
                            f"enable_bitmapscan={self.enable_bitmapscan}")
        result_list.append(res)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
            f"enable_seqscan={self.enable_seqscan}")
        result_list.append(res)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
            f"opfusion_debug_mode={self.opfusion_debug_mode}")
        result_list.append(res)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
            f"enable_pbe_optimization={self.enable_pbe_optimization}")
        result_list.append(res)

        sql = f"drop table {self.t_name} cascade;"
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        status = self.commonsh.get_db_cluster_status("detail")
        self.log.info(status)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, result,
                      "执行失败:" + text)
        for res in result_list:
            self.assertTrue(res, "执行失败:" + text)
        self.assertTrue("Normal" in status or "Degraded" in status)
        self.log.info(f"-----{os.path.basename(__file__)} finish=")
